package net.qunzhi.common.utils;

import net.qunzhi.common.exception.RRException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.DecimalFormat;

public class ExcelReader {
	
	/**   
	 * @Title: getType   
	 * @Description: 获取上传excel的版本以及判空
	 * @param: @param fileName
	 * @param: @param file
	 * @param: @return
	 * @param: @throws Exception      
	 * @return: Sheet      
	 * @throws   
	 */
	public static Sheet getType(String fileName, MultipartFile file) throws Exception {
		
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			throw new RRException("上传文件格式不正确");
		}
		boolean isExcel2003 = true;
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}
		InputStream is = file.getInputStream();
		Workbook workbook = null;
		if (isExcel2003) {
			workbook = new HSSFWorkbook(is);
		} else {
			workbook = new XSSFWorkbook(is);
		}
		Sheet sheet = workbook.getSheetAt(0);
		if (sheet == null) {
			throw new RRException("空文件！");
		}
		
		return sheet;
		
	}
	
	/**   
	 * @Title: checkHeader   
	 * @Description: 验证表头是否正确，传入表头前三个拼起来的字符串。
	 * @param: @param heads
	 * @param: @param sheet
	 * @param: @throws Exception      
	 * @return: void      
	 * @throws   
	 */
	public static void checkHeader(String heads, Sheet sheet) throws Exception {
		Row row = sheet.getRow(0);
		String[] columns = heads.split(",");
		for (int i = 1; i < 3; i++) {
			if (!row.getCell(i).toString().replaceAll("\\s*", "").equals(columns[i])) {
				throw new RRException("导入文件格式正确。");
			}
		}
		
	}
	/**   
	 * @Title: getValue   
	 * @Description: 根据不同的类型返回字符串类型  
	 * @param: @param cell
	 * @param: @return      
	 * @return: String      
	 * @throws   
	 */
	public static String getValue(Cell cell) {
	      if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
	          // 返回布尔类型的值
	          return String.valueOf(cell.getBooleanCellValue());
	      } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
	          // 返回数值类型的值
	          Object inputValue = null;// 单元格值
	          Long longVal = Math.round(cell.getNumericCellValue());
	          Double doubleVal = cell.getNumericCellValue();
	          if(Double.parseDouble(longVal + ".0") == doubleVal){   //判断是否含有小数位.0
	              inputValue = longVal;
	          }
	          else{
	              inputValue = doubleVal;
	          }
	          DecimalFormat df = new DecimalFormat("#.##");    //格式化为四位小数，按自己需求选择；
	          return df.format(inputValue);      //返回String类型
	      } else if (cell.getCellType() == cell.CELL_TYPE_FORMULA) {
	          // 返回字符串类型的值
			  try {
				  return String.valueOf(cell.getNumericCellValue());
			  } catch (IllegalStateException e) {
				  return String.valueOf(cell.getRichStringCellValue());
			  }
	      } else {
	          // 返回字符串类型的值
	          return String.valueOf(cell.getStringCellValue());
	      }
	  }
}
